<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
class Class_T_PATTERN extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table = "T_PATTERN";
	protected $_table_d = "T_PATTERN_D";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey = "ins_shop_shop_cd";
	
	  protected $_tab_last_id = "";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	// protected $_order = "id";
	 
	 protected $_cols     = array(
	 	"ins_shop_shop_cd"  => array(AOKI_VARTYPE_STR),
		"pper"              => array(AOKI_VARTYPE_STR),
		"pnums"             => array(AOKI_VARTYPE_STR),
		"pmemo"             => array(AOKI_VARTYPE_STR)
	 );
	
	
	 protected $_checks  = array(
		"ins_shop_shop_cd"  => array( "EXIST" ),
		"pper"              => array( "EXIST", "NUM" ),
		"pnums"             => array( "EXIST", "NUM" )
		
	 );
	 
	 //maxlength 
	 protected $_maxlens  = array(
		"pper"      =>  10 ,
		"pnums "   =>   10 
	 );
	 
	function setTableNameWeek($initid = "" ){
		$this->_tab_last_id = $initid;
		$this->_table       = $this->_table.$initid;
		$this->_table_d       = $this->_table_d.$initid;
	}
	
	function isExitsTable($intablename ="") {
		if ( empty( $intablename ) ) {
			$tabname = $this->_table;
			$sql = "show tables like '{$tabname}' ";
		} else {
			$sql = "show tables like '{$intablename}' ";
		}
		
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			return true;
		} else {
			return false;
		}
	}	
	public function doSaveH($values) {
		$keyID = $values["ins_shop_shop_cd"] ;
		$quotekeyID =  $this->quote( $keyID );
		
		$exitsWhere = "ins_shop_shop_cd = '{$quotekeyID}' ";
		
		if ($this->exits($exitsWhere)) {
			return $this->edit($keyID,$values);
			
		} else {
			return $this->add($values);
		}
	}
	
	public function doSaveD($keyID, $values) {
		$delsql = "DELETE FROM {$this->_table_d} WHERE ins_shop_shop_cd = '{$keyID}' ";
		$inssql = "INSERT INTO {$this->_table_d}(ins_shop_shop_cd,pattern_cd,pattern_name,pnums) values ";
		foreach ($values as $k=>$v) {
			$ins_shop_shop_cd = $keyID;
			$pattern_cd       = $v["pattern_cd"];
			$pattern_name     = $v["pattern_name"];
			$pnums            = $v["pnums"];
			$insVajues[] = "('{$ins_shop_shop_cd}','{$pattern_cd}','{$pattern_name}',{$pnums})";
		}
		
		$inssql = $inssql.implode(' , ', $insVajues);
		
		try {
			if (!$this->_db->query( $delsql )) {
				return FALSE;
			}
			if (!$this->_db->query( $inssql )) {
				return FALSE;
			}
			return TRUE;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return FALSE;
		}
	}
	
	private function getSearchTable($flg = 0,$innerjoin =false ) {
		$m_ins_shop_shop = "M_INS_SHOP_SHOP".$this->_tab_last_id;
		$fromtable = " FROM {$m_ins_shop_shop}  a INNER JOIN  M_INS_SHOP b on a.ins_shop_cd = b.ins_shop_cd " .
				" and (  b.stop_date = 0 or  curdate()+0 < b.stop_date )  ";
		if ($flg == 4 ) {
			$ins_co_cd = $_SESSION['ADMIN_LOGIN']['STAFF_INS_CO_CD'] ;
			$fromtable = $fromtable." and b.ins_co_cd = '{$ins_co_cd}' ";
		}
		
	 	$fromtable = $fromtable." INNER JOIN M_SHOP c on a.shop_cd = c.shop_cd  ";
	 	if  ($flg == 2) {
	 		$areaid =  $_SESSION['ADMIN_LOGIN']['STAFF_SEQ'] ;
	 		$fromtable = $fromtable."  and c.area_aja ={$areaid} ";
	 	}
	 	if  ($flg == 3) {
	 		$zoneid =  $_SESSION['ADMIN_LOGIN']['STAFF_SEQ'] ;
	 		$fromtable = $fromtable."  and c.zone_aja ={$zoneid} ";
	 	}
	 	if  ($innerjoin){
	 		$fromtable = $fromtable." INNER JOIN {$this->_table} d on a.ins_shop_shop_cd = d.ins_shop_shop_cd";
	 	}else {
	 		$fromtable = $fromtable." LEFT JOIN {$this->_table} d on a.ins_shop_shop_cd = d.ins_shop_shop_cd";
	 	}
	 	
	 	return $fromtable;
	}
	
	
	public function getsearch( $where, $flg = 0){
	 	$fromtable = $this->getSearchTable( $flg );
	 	$select    = " SELECT  a.ins_shop_shop_cd, " .
	 			"b.*," .
	 			"c.shop_cd,c.shop_name,d.pper,d.pnums,d.pmemo ";
	 	$order     = " ORDER BY c.shop_cd,a.ins_shop_cd ";
	 	
		$where = implode(' AND ', $where);
		if ($where) {
			$where = " WHERE ".$where;
		}
		$sql =  $select.$fromtable.$where.$order ;
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	public function getsearchCsv( $where, $flg = 0){
		$m_ins_shop_shop = "M_INS_SHOP_SHOP".$this->_tab_last_id;
		$fromtable = " FROM {$m_ins_shop_shop}  a INNER JOIN  M_INS_SHOP b on a.ins_shop_cd = b.ins_shop_cd and (  b.stop_date = 0 or  curdate()+0 < b.stop_date )  ";
		if ($flg == 4 ) {
			$ins_co_cd = $_SESSION['ADMIN_LOGIN']['STAFF_INS_CO_CD'] ;
			$fromtable = $fromtable." and b.ins_co_cd = '{$ins_co_cd}' ";
		}
	 	$fromtable = $fromtable." INNER JOIN M_SHOP c on a.shop_cd = c.shop_cd  ";
	 	$fromtable = $fromtable." INNER JOIN {$this->_table} d on a.ins_shop_shop_cd = d.ins_shop_shop_cd";
	 	$fromtable = $fromtable. " left join  M_INS_SHOP_RHIZOME re on re.ins_shop_cd = a.ins_shop_cd and re.matrix_cd = {$this->_tab_last_id} ";
	 	$fromtable = $fromtable. " inner join  M_LOGIN uz on uz.id = c.zone_aja  inner join  M_LOGIN ua on ua.id = c.area_aja";
	 	
	 	$select    = " SELECT  a.ins_shop_shop_cd,b.media_name,b.delive_name,b.local_name, " .
	 			"b.media_cd,b.ins_shop_cd,b.ins_shop_name,b.insert_conums,b.aoki_compete,b.shop_away,b.home_percent,b.all_amount,b.sale_share," .
	 			"re.now_quantity, re.last_quantity, re.diff_quantity, re.rate_quantity, re.now_sales, re.last_sales, re.diff_sales, " .
	 			"re.rate_sales, re.now_price, re.last_price, re.diff_price, re.rate_price," .
	 			"c.shop_cd,c.shop_name,uz.login_id as zone_cd,uz.user_name as zone_nm,ua.login_id as area_cd,ua.user_name as area_nm, " .
	 			"d.pper,d.pnums,d.pmemo ,d.u_time,d.c_time,(select  user_name FROM M_LOGIN where id  = d.u_user) as m_user_name";
	 	$order     = " ORDER BY c.shop_cd,a.ins_shop_cd ";
	 	
		$where = implode(' AND ', $where);
		if ($where) {
			$where = " WHERE ".$where;
		}
		$sql =  $select.$fromtable.$where.$order ;
		
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getinfo( $id ,$pre){
		$theid = $this->quote( $id );
		$sql1    = " SELECT * from M_INS_SHOP a where ins_shop_cd = '{$theid}'  ";
		$sql2    = " SELECT now_quantity, last_quantity, diff_quantity, rate_quantity, now_sales, last_sales, diff_sales, rate_sales, " .
				"now_price, last_price, diff_price, rate_price" .
				" from M_INS_SHOP_RHIZOME  where ins_shop_cd = '{$theid}' and  matrix_cd ={$pre} ";
		$data1   = array();
		$data2   = array();
		try {
			$this->_db->query( $sql1 );
			
			if ( $row = $this->_db->fetch() ){
				$data1 =  $row;
			}
			$this->_db->query( $sql2 );
			if ( $row = $this->_db->fetch() ){
				$data2 =  $row;
			}
			return array_merge($data1, $data2);
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getPattenNums( $where, $flg = 0){
	 	$fromtable =  $this->getSearchTable( $flg );
	 	$fromtable = $fromtable." INNER JOIN {$this->_table_d} e on d.ins_shop_shop_cd =e.ins_shop_shop_cd";
	 	$select    = " SELECT e.ins_shop_shop_cd,e.pattern_cd,e.pnums ";
	 	$where = implode(' AND ', $where);
	 	
	 	if ($where) {
	 		$where = " WHERE ".$where;
			}
	 	$sql =  $select.$fromtable.$where ;
	 	
		try {
			
			$this->_db->query( $sql );
			$data = array();
			while ( $row = $this->_db->fetch() ){
				$data[$row["ins_shop_shop_cd"]][$row["pattern_cd"]] = $row["pnums"];
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	 }
	 
	public function getShops( $patterns, $flg = 0){
		$fromtable  = $this->getSearchTable( $flg,true );
		$fromP      = " $fromtable  INNER JOIN {$this->_table_d} e on e.ins_shop_shop_cd = d.ins_shop_shop_cd " ;
		$select     = " SELECT  d.shop_cd,e.pattern_cd, sum(e.pnums) as pnums " ;
		$group_sort = " GROUP BY  d.shop_cd,e.pattern_cd order by  d.shop_cd,e.pattern_cd ";
		
		$sql = $select.$fromP.$group_sort;
		try {
			$this->_db->query( $sql );
			$pdatanums      = array();
			while( $row = $this->_db->fetch() ){
				$pdatanums[$row["shop_cd"]][$row["pattern_cd"]] = $row["pnums"];
			}
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
		
		//$fromtable =  $this->getSearchTable( $flg );
		
		$fromtable = $fromtable. " inner join  M_LOGIN uz on uz.id = c.zone_aja  inner join  M_LOGIN ua on ua.id = c.area_aja" ;
		$select     =  " SELECT  a.shop_cd, c.shop_name, " .
				" uz.login_id as zone_cd,uz.user_name as zone_nm,ua.login_id as area_cd,ua.user_name as area_nm," .
				" max(d.c_time) as c_time ,max(d.u_time) as u_time  " ;
		
		$group_sort =  " GROUP BY  a.shop_cd,c.shop_name order by  a.shop_cd ";
		
		$sql = $select.$fromtable.$group_sort;
		
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row ;
			}
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}

		foreach ($data as $k=>$v) {
			foreach ($patterns as $kk=>$vv) {
				if (isset($pdatanums[$v["shop_cd"]][$vv["pattern_cd"]])) {
					$data[$k][$vv["col"]] = $pdatanums[$v["shop_cd"]][$vv["pattern_cd"]];
				}
			}
			
			$data[$k]["u_time"] = empty($v["u_time"])? "" :date("Y/m/d",strtotime($v["u_time"]));
		}
		
		return $data;
	 }
	
	public function getDelives( $patterns, $flg = 0) {
		
		$matrix_cd =  $this->_tab_last_id;
		$fromtable =  $this->getSearchTable( $flg, true );
		
		$fromP      = $fromtable." INNER JOIN {$this->_table_d} e on e.ins_shop_shop_cd = d.ins_shop_shop_cd " ;
		$select     = " SELECT    a.shop_cd, b.delive_cd,b.media_cd ,e.pattern_cd, Sum(e.pnums) as pnums " ;
		$group_sort = " GROUP BY  a.shop_cd, b.delive_cd,b.media_cd , e.pattern_cd order by  a.shop_cd,e.pattern_cd ";
		$sql = $select.$fromP.$group_sort;
		
		try {
			$this->_db->query( $sql );
			$pdatanums      = array();
			while( $row = $this->_db->fetch() ){
				$pdatanums[$row["shop_cd"]][$row["delive_cd"]][$row["media_cd"]][$row["pattern_cd"]] = $row["pnums"];
			}
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
		
		//$fromtable  = $fromtable." INNER JOIN (select ma.shop_cd,ml.user_name from M_AJAREKS ma inner join  M_LOGIN ml on ma.userid  = ml.id  and ml.auth = '3')  g  on g.shop_cd = a.shop_cd ";
		$select     =  " SELECT  a.shop_cd, b.delive_cd,c.shop_name, b.delive_name,b.media_name,b.media_cd,md.close_day,md.close_time," .
					   " max(d.c_time) as c_time, max(d.u_time) as u_time " ;
		$group_sort =  " group by a.shop_cd, c.shop_name, b.media_cd, b.delive_cd " .
				" ORDER BY  a.shop_cd,b.media_cd,b.delive_cd  ";
		$fromtable = $fromtable." LEFT JOIN M_DELIVE md on md.delive_cd = b.delive_cd  and md.matrix_cd = {$matrix_cd} ";
		$sql = $select.$fromtable.$group_sort;
		
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row ;
			}
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
		
		foreach ($data as $k=>$v) {
			foreach ($patterns as $kk=>$vv) {
				if ( isset($pdatanums[$v["shop_cd"]][$v["delive_cd"]][$v["media_cd"]][$vv["pattern_cd"]]) ){
					$data[$k][$vv["col"]] = $pdatanums[$v["shop_cd"]][$v["delive_cd"]][$v["media_cd"]][$vv["pattern_cd"]];
				}
			}
		}
		return $data;
	 }
		
	 public function getpatterns($block = null ){	
		if ($block) {
			$sql  = "select a.pattern_cd,a.media_flg,a.pattern_name,CONCAT('p',a.pattern_cd) as col from M_PATTERN a " .
				" inner join  (select distinct pattern_cd from  M_PATTERN_BLOCK where block_cd = '{$block}' ) b on a.pattern_cd = b.pattern_cd " .
				" where disp_ng = 0 and  a.media_flg<>'2' and  a.media_flg<> '3'  " .
				" order by a.media_flg, a.pattern_cd";
		}else {
			$sql  = "select a.pattern_cd,a.media_flg,a.pattern_name,CONCAT('p',a.pattern_cd) as col from M_PATTERN a" .
				" where disp_ng = 0 and  a.media_flg<>'2' and  a.media_flg<> '3'  " .
				" order by a.media_flg, a.pattern_cd";
		}
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return FALSE;
		}
	 }
	 
	public function copyto($toWeek ){
		$toTable = "T_PATTERN" . $toWeek;
		$fromTable = $this->_table;
		$loginuser = $_SESSION['ADMIN_LOGIN']['STAFF_SEQ'];
		$sqlHead = "insert into {$toTable}( ins_shop_shop_cd, shop_cd, ins_shop_cd ,pper ,pnums ,pmemo, u_time, u_user, c_time, c_user)
			select  ins_shop_shop_cd, shop_cd, ins_shop_cd ,pper ,pnums ,pmemo,CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}'
			from  {$fromTable} ";
		
		$toTable = "T_PATTERN" . $toWeek;
		$fromTable = $this->_table;
		
		$sqlDetail = "insert into {$toTable}( ins_shop_shop_cd, shop_cd, ins_shop_cd, pper, pnums, pmemo ,pmemo, u_time, u_user, c_time, c_user)
			select ins_shop_shop_cd, shop_cd, ins_shop_cd, pper, pnums, pmemo,CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}'
			from  {$fromTable} ";
		$this->begin_trans();
		$db = $this->_db;
		try {
			if (!$db->query( $sqlHead )) {
				$updateFlg = true;
			}
		
			if (!$db->query( $sqlDetail )) {
				$updateFlg = true;
			}

			if ( $updateFlg ){
				$this->commit_trans();
				return true;
			} else {
				$this->rollback_trans();
				return false;
			}
			
		} catch (Exception $e) {
				echo "例外キャッチ：", $e->getMessage(), "\n";
				$this->rollback_trans();
				return false;
		}
		
	}
	 
}

?>